package com.shadow.demo.excel.poi;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

import com.shadow.demo.common.constants.NumericConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.hutool.core.date.DatePattern;

/**
 * Microsoft Office excel Helper.
 */
@Slf4j
public final class MSExcelHelper {

    private MSExcelHelper() {
    }

    /**
     * 给定一个xsl InputStream，该函数会忽略空行，空列会添加一个空字符串代替.
     * @param is excel文件输入流
     * @param sheetIndex sheet索引
     * @param columns 列索引
     * @return java.util.List
     * @exception IOException IOException
     */
    public static List<List<String>> readXlsText(
            final InputStream is,
            final int sheetIndex,
            final int columns
    ) throws IOException {
        List<List<String>> rows = new ArrayList<>();
        List<String> cells = null;
        HSSFWorkbook work = new HSSFWorkbook(is);
        // 获取xsl的工作区数量
        int sheets = work.getNumberOfSheets();
        int cellLength = 0;
        if (sheets < sheetIndex) {
            return rows;
        }
        // 获取指定位置的工作区
        HSSFSheet sheet = work.getSheetAt(sheetIndex);
        HSSFRow row = null;
        Iterator<Row> iteRow = sheet.rowIterator();
        while (iteRow.hasNext()) {
            row = (HSSFRow) iteRow.next();
            cells = new ArrayList<>();
            cellLength = (columns == -1) ? row.getLastCellNum() : columns;
            for (int j = 0; j <= cellLength; j++) {
                Cell cell = row.getCell(j);
                if (cell != null && CellType.NUMERIC == cell.getCellType()) {
                    DecimalFormat df = new DecimalFormat(NumericConstant.STRING_ZERO);
                    cells.add(df.format(cell.getNumericCellValue()));
                } else {
                    cells.add(cell == null ? "" : cell.toString());
                }
            }
            // 将解析的一行添加至集合
            rows.add(cells);
        }
        is.close();
        return rows;
    }

    /**
     * 获取xls的第1个sheet的内容.
     * @param classPathFileName xls相对于ClassPath的路径
     * @return java.util.List
     */
    public static List<List<String>> getXlsText(final String classPathFileName) throws IOException {
        return getXlsText(classPathFileName, 0, -1);
    }

    /**
     * 给定一个xsl 文件的路径，该函数会忽略空行，空列会添加一个空字符串代替.
     * @param classPathFileName 相对于ClassPath的文件路径
     * @param sheetIndex 指定解析第几个sheet的工作区
     * @param columns 指定每行的列数，如果传入-1， 则根据内容来判断。
     * @return List 将内容转换为集合
     */
    public static List<List<String>> getXlsText(
            final String classPathFileName,
            final int sheetIndex,
            final int columns
    ) throws IOException {
        InputStream is = MSExcelHelper.class.getResourceAsStream(classPathFileName);
        return readXlsText(is, sheetIndex, columns);
    }

    /**
     * 给定一个xlsx InputStream，该函数会忽略空行，空列会添加一个空字符串代替.
     * @param is excel文件输入流
     * @param sheetIndex sheet索引
     * @param columns 列索引
     * @return java.util.List
     * @exception IOException IOException
     */
    public static List<List<String>> readXlsxText(
            final InputStream is,
            final int sheetIndex,
            final int columns
    ) throws IOException {
        final List<List<String>> rows = new ArrayList<>();
        List<String> cells = null;
        final XSSFWorkbook work = new XSSFWorkbook(is);
        // 获取xsl的工作区数量
        int sheets = work.getNumberOfSheets();
        int cellLength = 0;
        if (sheets < sheetIndex) {
            return rows;
        }
        // 获取指定位置的工作区
        final XSSFSheet sheet = work.getSheetAt(sheetIndex);
        XSSFRow row = null;
        Iterator<Row> iteRow = sheet.rowIterator();
        while (iteRow.hasNext()) {
            row = (XSSFRow) iteRow.next();
            cells = new ArrayList<>();
            cellLength = (columns == -1) ? row.getLastCellNum() : columns;
            for (int j = 0; j <= cellLength; j++) {
                Cell cell = row.getCell(j);
                if (cell != null && CellType.NUMERIC == cell.getCellType()) {
                    DecimalFormat df = new DecimalFormat(NumericConstant.STRING_ZERO);
                    cells.add(df.format(cell.getNumericCellValue()));
                } else {
                    cells.add(cell == null ? "" : cell.toString());
                }
            }
            // 将解析的一行添加至集合
            rows.add(cells);
        }
        is.close();
        return rows;
    }

    /**
     * 将数据写入Excel表中xls格式.
     * @param workbook 工作簿
     * @param data 数据列表
     */
    public static void writeTextForXls(
            final HSSFWorkbook workbook,
            final List<List<Object>> data
    ) {
        final HSSFSheet sheet = workbook.createSheet();
        if (CollectionUtils.isNotEmpty(data)) {
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(i);
                List<Object> tmp = data.get(i);
                if (CollectionUtils.isNotEmpty(tmp)) {
                    for (int j = 0; j < tmp.size(); j++) {
                        setHssfCellValue(tmp.get(j), row.createCell(j));
                    }
                }
            }
        }
    }

    /**
     * 将数据写入Excel表中xlsx格式.
     * @param workbook 工作簿
     * @param data 数据列表
     */
    public static void writeTextForXlsx(
            final XSSFWorkbook workbook,
            final List<List<Object>> data
    ) {
        final XSSFSheet sheet = workbook.createSheet();
        if (CollectionUtils.isNotEmpty(data)) {
            for (int i = 0; i < data.size(); i++) {
                XSSFRow row = sheet.createRow(i);
                List<Object> tmp = data.get(i);
                if (CollectionUtils.isNotEmpty(tmp)) {
                    for (int j = 0; j < tmp.size(); j++) {
                        setXssfCellValue(tmp.get(j), row.createCell(j));
                    }
                }
            }
        }
    }

    /**
     * 生成sheet表.
     * @param workbook sheet对象
     * @param data 数据列表
     * @return HSSFSheet
     */
    public static HSSFSheet writeSheetTextForxls(
            final HSSFWorkbook workbook,
            final List<List<Object>> data)
    {
        final HSSFSheet sheet = workbook.createSheet();
        if (CollectionUtils.isNotEmpty(data)) {
            for (int i = 0; i < data.size(); i++) {
                final HSSFRow row = sheet.createRow(i);
                final List<Object> tmp = data.get(i);
                if (CollectionUtils.isNotEmpty(tmp)) {
                    for (int j = 0; j < tmp.size(); j++) {
                        setHssfCellValue(tmp.get(j), row.createCell(j));
                    }
                }
            }
        }
        return sheet;
    }

    /**
     * 设置HSS单元格的参数值.
     * @param value 参数值
     * @param cell 单元格对象
     */
    private static void setHssfCellValue(final Object value, final HSSFCell cell) {
        cell.setCellType(CellType.STRING);
        cell.setCellValue(new HSSFRichTextString(value == null ? "" : value.toString()));
    }

    /**
     * 设置XSS单元格的参数值.
     * @param value 参数值
     * @param cell 单元格对象
     */
    private static void setXssfCellValue(final Object value, final XSSFCell cell) {
        cell.setCellValue(new XSSFRichTextString(value == null ? "" : value.toString()));
    }

    /**
     * 导出数据.
     * @author Pbody
     * @date 17:24 2021/11/3
     * @param response 响应对象
     * @param excelData 导出数据
     * @param name 文件名称
     * @param columnWidth 单元格宽度，一般为25
     **/
    public static void exportData(
            final HttpServletResponse response,
            final List<List<Object>> excelData,
            final String name,
            final int columnWidth
    ) {
        final XSSFWorkbook book = new XSSFWorkbook();
        OutputStream os = null;
        try {
            writeTextForXlsx(book, excelData);
            //设置列宽
            final XSSFSheet sheet0 = book.getSheet("Sheet0");
            sheet0.setDefaultColumnWidth(columnWidth);
            final String fileName = name + DatePattern.NORM_DATETIME_FORMATTER.format(LocalDateTime.now());
            os = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition",
                    "attachment; filename=" + new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1) + ".xlsx");
            response.setContentType("application/msexcel");
            book.write(os);
        } catch (IOException ioException) {
            log.error(ioException.getMessage());
        } finally {
            try {
                if (null != os) {
                    os.close();
                }
                book.close();
            } catch (IOException ioException) {
                log.error(ioException.getMessage());
            }
        }
    }
}